#ifndef MYSQLCOMMANDS_H
#define MYSQLCOMMANDS_H

const char * CREATE_DATABASE = "CREATE DATABASE richter;";

const char * CREATE_MATERIAL_TABLE = "CREATE TABLE material ( "
        "id INT AUTO_INCREMENT, "
        "name varchar(750), "
        "number INT, "
        "PRIMARY KEY (id) "
        ") ENGINE=INNODB;";

const char * CREATE_MATERIAL_SPEC_META_TABLE = "CREATE TABLE material_spec_meta ("
    "id INT AUTO_INCREMENT, "
    "name VARCHAR(750), "
    "type ENUM('int', 'string', 'float', 'date'), "
    "PRIMARY KEY(id, name), "
    "FOREIGN KEY (id) "
     " REFERENCES material(id) "
        " ON DELETE CASCADE "
        " ) ENGINE=INNODB; ";

const char * CREATE_STATE_TABLE = "CREATE TABLE state ( "
        "id INT AUTO_INCREMENT, "
    "description varchar(750), "
    "PRIMARY KEY (id) "
        ") ENGINE=INNODB;";

const char * CREATE_PLACES_TABLE = "CREATE TABLE places ( "
    "id INT AUTO_INCREMENT, "
    "description varchar(750), "
    "PRIMARY KEY (id) "
        ") ENGINE=INNODB;";

const char * CREATE_CHARGE_TABLE = "CREATE TABLE charge ( "
    "id VARCHAR(10), "
    "number INT, "
    "chargeCounter VARCHAR(10), "
    "start DATE, "
    "matId INT, "
    "weight FLOAT, "
    "stateId INT, "
    "placeId INT, "
    "PRIMARY KEY (id), "
    "FOREIGN KEY (matId) "
        "REFERENCES material(id) "
        "ON DELETE CASCADE, "
    "FOREIGN KEY (stateId) "
        "REFERENCES state(id) "
        "ON DELETE CASCADE, "
    "FOREIGN KEY (placeId) "
        "REFERENCES places(id) "
        "ON DELETE CASCADE "
        ") ENGINE=INNODB;";

const char * CREATE_MATERIAL_SPEC_TABLE = "CREATE TABLE material_spec ( "
    "chargeId VARCHAR(10), "
    "name VARCHAR(750), "
    "int_type INT, "
    "text_type VARCHAR(750), "
    "float_type FLOAT, "
    "date_type DATE, "
    "PRIMARY KEY(chargeId, name), "
    "FOREIGN KEY(chargeId) "
        "REFERENCES charge(id) "
        "ON DELETE CASCADE "
") ENGINE=INNODB;";


const char * CREATE_PRODUCING_TABLE = "CREATE TABLE producing ( "
    "id VARCHAR(10), "
    "input VARCHAR(10), "
    "weight FLOAT, "
    "PRIMARY KEY(id, input), "
    "FOREIGN KEY (id) "
        "REFERENCES charge(id) "
        "ON DELETE CASCADE, "
    "FOREIGN KEY (input) "
        "REFERENCES charge(id) "
        "ON DELETE CASCADE "
") ENGINE=INNODB;";

const char * CREATE_AUTH_TABLE = "CREATE TABLE auth ( "
    "id INT AUTO_INCREMENT, "
    "description VARCHAR(750), "
    "PRIMARY KEY(id) "
        ") ENGINE=INNODB;";

const char * CREATE_USERS_TABLE = "CREATE TABLE users ( "
    "id INT AUTO_INCREMENT, "
    "name VARCHAR(750), "
    "password VARCHAR(50), "
    "PRIMARY KEY(id) "
        ") ENGINE=INNODB; ";

const char * CREATE_AUTH_DATA_TABLE = "CREATE TABLE auth_data ( "
    "user_id INT, "
    "auth_id INT, "
    "PRIMARY KEY(user_id, auth_id), "
    "FOREIGN KEY (user_id) "
        "REFERENCES users(id) "
        "ON DELETE CASCADE, "
    "FOREIGN KEY (auth_id) "
        "REFERENCES auth(id) "
        "ON DELETE CASCADE "
        ") ENGINE=INNODB;";

#endif // MYSQLCOMMANDS_H

